package com.eveinfo.sql;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

import com.eveinfo.sql.data.Attribute;
import com.eveinfo.sql.data.Item;
import com.eveinfo.sql.data.ItemAttributes;
import com.eveinfo.sql.data.SkillsDB;
import com.eveinfo.sql.data.SkillsDB.SkillGroup;

public class DataBaseHelper extends SQLiteOpenHelper {

	// The Android's default system path of your application database.
	private static String DB_PATH = "/sdcard/eveinfo/";
	private static String DB_NAME = "dominion.db";
	private SQLiteDatabase myDataBase;
	private final Context myContext;

	/**
	 * Constructor Takes and keeps a reference of the passed context in order to
	 * access to the application assets and resources.
	 * 
	 * @param context
	 */
	public DataBaseHelper(Context context) {
		super(context, DB_NAME, null, 1);
		this.myContext = context;
	}

	/**
	 * Creates a empty database on the system and rewrites it with your own
	 * database.
	 * */
	public void createDataBase() throws IOException {
		boolean dbExist = checkDataBase();
		if (dbExist) {
			// do nothing - database already exist
		} else {
			// By calling this method and empty database will be created into
			// the default system path
			// of your application so we are gonna be able to overwrite that
			// database with our database.
			this.getReadableDatabase();

			try {
				copyDataBase();
			} catch (IOException e) {
				throw new Error("Error copying database");
			}
		}
	}

	/**
	 * Check if the database already exist to avoid re-copying the file each
	 * time you open the application.
	 * 
	 * @return true if it exists, false if it doesn't
	 */
	private boolean checkDataBase() {
		SQLiteDatabase checkDB = null;
		try {
			String myPath = DB_PATH + DB_NAME;
			checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
		} catch (SQLiteException e) {
			// database does't exist yet.
		}
		if (checkDB != null) {
			checkDB.close();
		}
		return checkDB != null ? true : false;
	}

	/**
	 * Copies your database from your local assets-folder to the just created
	 * empty database in the system folder, from where it can be accessed and
	 * handled. This is done by transfering bytestream.
	 * */
	private void copyDataBase() throws IOException {
		// Open your local db as the input stream
		InputStream myInput = myContext.getAssets().open(DB_NAME);

		// Path to the just created empty db
		String outFileName = DB_PATH + DB_NAME;

		// Open the empty db as the output stream
		OutputStream myOutput = new FileOutputStream(outFileName);

		// transfer bytes from the inputfile to the outputfile
		byte[] buffer = new byte[1024];
		int length;
		while ((length = myInput.read(buffer)) > 0) {
			myOutput.write(buffer, 0, length);
		}

		// Close the streams
		myOutput.flush();
		myOutput.close();
		myInput.close();

	}

	public void openDataBase() throws SQLException {
		String myPath = DB_PATH + DB_NAME;
		myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);

	}

	@Override
	public synchronized void close() {
		if (myDataBase != null)
			myDataBase.close();
		super.close();
	}

	@Override
	public void onCreate(SQLiteDatabase db) {

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

	}

	public String getTypeName(int typeID) {
		String result = null;
		Cursor query = myDataBase.query("invTypes", new String[] { "*" }, "typeID = " + typeID, null, null, null, null);
		if (query != null) {
			int column = query.getColumnIndex("typeName");
			while (query.moveToNext()) {
				result = query.getString(column);
				break;
			}
			query.close();
		}
		return result;
	}

	public String getGroupName(int groupID) {
		String result = null;
		Cursor query = myDataBase.query("invGroups", new String[] { "*" }, "groupID = " + groupID, null, null, null,
				null);
		if (query != null) {
			int column = query.getColumnIndex("groupName");
			while (query.moveToNext()) {
				result = query.getString(column);
				break;
			}
			query.close();
		}
		return result;
	}

	public ArrayList<SkillGroup> getSkills() {
		SkillsDB skillsDB = new SkillsDB();
		ArrayList<SkillGroup> groups = new ArrayList<SkillGroup>();
		Cursor query = myDataBase
				.query(
						"( SELECT * FROM invGroups WHERE categoryID = 16 AND published = 1 ) AS groups INNER JOIN invTypes ON ( invTypes.groupID = groups.groupID AND invTypes.published = 1 )",
						new String[] { "groups.groupName AS groupName", "invTypes.typeID AS typeID",
								"invTypes.typeName AS typeName" }, null, null, null, null, "groupName");
		if (query != null) {
			int groupName = query.getColumnIndex("groupName");
			int typeID = query.getColumnIndex("typeID");
			int typeName = query.getColumnIndex("typeName");
			while (query.moveToNext()) {
				String group = query.getString(groupName);
				int skillID = query.getInt(typeID);
				String skillName = query.getString(typeName);
				boolean isAdd = false;
				for (SkillGroup skillGroup : groups) {
					if (skillGroup.getName().equals(group)) {
						skillGroup.addSkill(skillsDB.new Skill(skillID, skillName));
						isAdd = true;
						break;
					}
				}
				if (!isAdd) {
					groups.add(skillsDB.new SkillGroup(group, skillsDB.new Skill(skillID, skillName)));
				}
			}
			query.close();
		}
		return groups;
	}

	/*
	 * Item
	 */

	public Item getItem(int typeID) {
		Item result = null;
		Cursor query = myDataBase.query("invTypes, eveGraphics, invGroups",
				new String[] { "invTypes.*, categoryID, icon" },
				"invTypes.groupID = invGroups.groupID AND invTypes.graphicID = eveGraphics.graphicID AND invTypes.typeID = "
						+ typeID, null, null, null, null);
		if (query != null) {
			while (query.moveToNext()) {
				int groupIDColumn = query.getColumnIndex("groupID");
				int typeNameColumn = query.getColumnIndex("typeName");
				int descriptionColumn = query.getColumnIndex("description");
				int graphicIDColumn = query.getColumnIndex("graphicID");
				int radiusColumn = query.getColumnIndex("radius");
				// int massColumn = query.getColumnIndex("mass");
				int volumeColumn = query.getColumnIndex("volume");
				int capacityColumn = query.getColumnIndex("capacity");
				// int portionSizeColumn = query.getColumnIndex("portionSize");
				int raceIDColumn = query.getColumnIndex("raceID");
				int basePriceColumn = query.getColumnIndex("basePrice");
				int publishedColumn = query.getColumnIndex("published");
				int marketGroupIDColumn = query.getColumnIndex("marketGroupID");
				int chanceOfDuplicatingColumn = query.getColumnIndex("chanceOfDuplicating");
				int categoryIDColumn = query.getColumnIndex("categoryID");
				int iconColumn = query.getColumnIndex("icon");
				int groupID = query.getInt(groupIDColumn);
				int categoryID = query.getInt(categoryIDColumn);
				String typeName = query.getString(typeNameColumn);
				String description = query.getString(descriptionColumn);
				int graphicID = query.getInt(graphicIDColumn);
				double radius = query.getDouble(radiusColumn);
				// double mass = query.getDouble(massColumn);
				double volume = query.getDouble(volumeColumn);
				double capacity = query.getDouble(capacityColumn);
				// int portionSize = query.getInt(portionSizeColumn);
				int raceID = query.getInt(raceIDColumn);
				double basePrice = query.getDouble(basePriceColumn);
				boolean published = (query.getInt(publishedColumn) == 1);
				int marketGroupID = query.getInt(marketGroupIDColumn);
				double chanceOfDuplicating = query.getDouble(chanceOfDuplicatingColumn);
				String icon = query.getString(iconColumn);
				result = new Item(typeID, groupID, categoryID, typeName, description, graphicID, radius, 0, volume,
						capacity, 0, raceID, basePrice, published, marketGroupID, chanceOfDuplicating, icon);
			}
			query.close();
		}
		return result;
	}

	/*
	 * Station
	 */

	public String getStationName(int stationID) {
		String result = null;
		Cursor query = myDataBase.query("staStations", new String[] { "stationName" }, "stationID = " + stationID,
				null, null, null, null);
		if (query != null) {
			int column = query.getColumnIndex("stationName");
			while (query.moveToNext()) {
				result = query.getString(column);
				break;
			}
			query.close();
		}
		return result;
	}

	/*
	 * Ship
	 */

	public ItemAttributes getItemAttributes(int itemID) {
		ItemAttributes result = null;
		Cursor query = myDataBase
				.query(
						"dgmTypeAttributes as entity INNER JOIN dgmAttributeTypes as dmgAttrib ON dmgAttrib.attributeID = entity.attributeID",
						new String[] { "*" }, "typeID = " + itemID, null, null, null, "dmgAttrib.categoryID");
		if (query != null) {
			result = new ItemAttributes();
			final int attributeIDColumn = query.getColumnIndex("attributeID");
			final int displayNameColumn = query.getColumnIndex("displayName");
			final int categoryIDColumn = query.getColumnIndex("categoryID");
			final int valueIntColumn = query.getColumnIndex("valueInt");
			final int valueFloatColumn = query.getColumnIndex("valueFloat");
			while (query.moveToNext()) {
				int attributeID = query.getInt(attributeIDColumn);
				String displayName = query.getString(displayNameColumn);
				int categoryID = query.getInt(categoryIDColumn);
				int valueInt = query.getInt(valueIntColumn);
				float valueFloat = query.getFloat(valueFloatColumn);
				if (valueInt == 0) {
					result.addAtributes(new Attribute(attributeID, displayName, categoryID, valueFloat));
				} else {
					result.addAtributes(new Attribute(attributeID, displayName, categoryID, valueInt));
				}
			}
			query.close();
		}
		return result;

	}

}
